﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace Txt2Shp4Sharp.lib
{
    public class SqliteHelper
    {
        private static readonly log4net.ILog m_log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        /// <summary>
        /// 数据库连接定义
        /// </summary>
        private SQLiteConnection dbConnection;

        /// <summary>
        /// SQL命令定义
        /// </summary>
        private SQLiteCommand dbCommand;

        public SqliteHelper() { 
            //
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接SQLite库字符串</param>
        public SqliteHelper(string connectionString)
        {
            try
            {
                dbConnection = new SQLiteConnection(connectionString);
                dbConnection.Open();
            }catch(Exception e)
            {
                Log(e.ToString());
            }
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlStr">SQL命令字符串</param>
        /// <returns></returns>
        public SQLiteDataReader executeQuery(String sqlStr)
        {
            SQLiteDataReader reader = null;
            try
            {
                this.dbCommand = this.dbConnection.CreateCommand();
                this.dbCommand.CommandText = sqlStr;

                reader = dbCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                m_log.Error(ex.Message);
                System.Console.WriteLine(ex.Message);
            }

            return reader;
        }
        

        /// <summary>
        /// 执行cmd操作命令,返回受影响的行数，自动销毁连接
        /// </summary>
        /// <param name="connectionString">sqlite 连接字符串</param>
        /// <param name="sqlCmd">sqlcmd操作命令</param>
        /// <returns></returns>
        public int executeCmd(String connectionString, String sqlCmd)
        {
            int affectedCount = -1;
            using (SQLiteConnection dbConnection = new SQLiteConnection(connectionString))
            {
                dbConnection.Open();
                try
                {
                    SQLiteCommand dbCommand = this.dbConnection.CreateCommand();
                    dbCommand.CommandText = sqlCmd;

                    SQLiteDataReader reader = dbCommand.ExecuteReader();
                    //-1表示没有行受操作影响
                    affectedCount = reader.RecordsAffected;
                }
                catch (Exception ex)
                {
                    m_log.Error(ex.Message);
                    System.Console.WriteLine(ex.Message);
                }
                finally
                {
                    dbConnection.Close();
                }
            }

            return affectedCount;
        }


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void closeConnection() {
            //销毁Command
            if (this.dbCommand != null) {
                this.dbCommand.Cancel();
            }
            this.dbCommand = null;


            //关闭Connection
            if (this.dbConnection != null) {
                this.dbConnection.Close();
            }

            this.dbConnection = null;
        }
        /// <summary>
        /// 读取整张数据表
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <returns></returns>
        public SQLiteDataReader readFullTable(string tableName)
        {
            string queryString = "SELECT * FROM " + tableName;
            return executeQuery(queryString);
        }
        
        /// <summary>
        /// 向指定数据表中插入数据
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="values">插入的数值集合</param>
        /// <returns></returns>
        public SQLiteDataReader insertValues(String tableName, String[] values) {
            //获取数据表中字段数目
            int fieldCount = readFullTable(tableName).FieldCount;
            //当插入的数据长度不等于字段数目时引发异常
            if (values.Length != fieldCount)
            {
                throw new SQLiteException("values.Length!=fieldCount");
            }

            string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
            for (int i = 1; i < values.Length; i++)
            {
                queryString += ", " + "'" + values[i] + "'";
            }
            queryString += " )";
            return executeQuery(queryString);
        }

      

        /// <summary>
        ///更新指定数据表内的数据 
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colValues">字段名</param>
        /// <param name="key">关键字</param>
        /// <param name="value">关键字对应的值</param>
        /// <param name="operation">运算符：=,<,>,...，默认“=”</param>
        /// <returns></returns>
        public SQLiteDataReader updateValues(String tableName, String[] colNames, String[] colValues, string key, string value, string operation = "=")
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length");
            }
            string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";
            }
            //条件
            queryString += " WHERE " + key + operation + "'" + value + "'";
            return executeQuery(queryString);
        }

        public SQLiteDataReader updateValues(String tableName, String[] colNames, String[] colValues, String whereStr)
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length");
            }
            string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";
            }
            //条件
            if (whereStr != null && whereStr.Length > 0) {
                queryString += " " + whereStr;
            }
            
            return executeQuery(queryString);
        }

        /// <summary>
        /// 删除指定数据表内的数据
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名集合</param>
        /// <param name="colValues">字段名对应的数据集合</param>
        /// <param name="operations"></param>
        /// <returns></returns>
        public SQLiteDataReader deleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
            }

            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";
            }
            return executeQuery(queryString);
        }
        /// <summary>
        ///  删除指定数据表内的数据
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="colNames">字段名集合</param>
        /// <param name="colValues">字段名对应的数据集合</param>
        /// <param name="operations"></param>
        /// <returns></returns>
        public SQLiteDataReader deleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)
        {
            //当字段名称和字段数值不对应时引发异常
            if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
            {
                throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
            }

            string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
            for (int i = 1; i < colValues.Length; i++)
            {
                queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
            }
            return executeQuery(queryString);
        }

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="colNames">字段名</param>
        /// <param name="colTypes">字段名类型</param>
        /// <returns></returns>
        public SQLiteDataReader createTable(string tableName, string[] colNames, string[] colTypes)
        {
            string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];
            for (int i = 1; i < colNames.Length; i++)
            {
                queryString += ", " + colNames[i] + " " + colTypes[i];
            }
            queryString += "  ) ";
            return executeQuery(queryString);
        }
        /// <summary>
        /// 读取表内容 Reads the table
        /// </summary>
        /// <param name="tableName">Table name</param>
        /// <param name="items">Items</param>
        /// <param name="colNames">Col names</param>
        /// <param name="operations">Operations</param>
        /// <param name="colValues">Col values</param>
        /// <returns></returns>
        public SQLiteDataReader readTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
        {
            string queryString = "SELECT " + items[0];
            for (int i = 1; i < items.Length; i++)
            {
                queryString += ", " + items[i];
            }
            queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
            for (int i = 0; i < colNames.Length; i++)
            {
                queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
            }
            return executeQuery(queryString);
        }
        public DataTable ConvertDataReaderToDataTable(DbDataReader dataReader) {
            DataTable dt = new DataTable();
            try
            {    
                //动态添加表的数据列  
                for (int i = 0; i < dataReader.FieldCount; i++){
                    DataColumn myDataColumn = new DataColumn();
                    myDataColumn.DataType = dataReader.GetFieldType(i);
                    myDataColumn.ColumnName = dataReader.GetName(i);
                    dt.Columns.Add(myDataColumn);
                }

                //添加表的数据  
                while (dataReader.Read()){
                    DataRow myDataRow = dt.NewRow();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        myDataRow[i] = dataReader[i].ToString();
                    }
                    dt.Rows.Add(myDataRow);
                    myDataRow = null;
                }
                //关闭数据读取器  
                dataReader.Close();

                return dt;
            }
            catch (Exception ex)
            {
                //抛出类型转换错误  
                //SystemError.CreateErrorLog(ex.Message);  
                throw new Exception(ex.Message, ex);
            }
        }

        /// <summary>
        /// 本类log
        /// </summary>
        /// <param name="s"></param>
        static void Log(string s)
        {
            Console.WriteLine("class SqLiteHelper:::" + s);
        }

    }
}
